package org.pih.migration.load.objects;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.Namespace;
import org.jdom.Text;
import org.jdom.input.SAXBuilder;
import org.pih.javarosa.migration.export.Row;
import org.pih.javarosa.migration.export.Worksheet;

public class WorksheetExtractor {

	@SuppressWarnings("unchecked")
	public List<Worksheet> extract(String filename) throws JDOMException, IOException {
		Document document = null;
		SAXBuilder saxBuilder = new SAXBuilder();
		saxBuilder.setValidation(false);
		saxBuilder.setExpandEntities(false);

		document = saxBuilder.build(new File(filename));			

		Namespace namespace = Namespace.getNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
		Element root = document.getRootElement();

		List<Element> worksheets = root.getChildren("Worksheet", namespace);

		List<Worksheet> dataRaw = new ArrayList<Worksheet>(); 

		for (Element worksheet : worksheets) {

			Worksheet worksheetRaw = new Worksheet();
			worksheetRaw.setName(worksheet.getAttributeValue("Name", namespace));
			List<Row> rowsRaw = new ArrayList<Row>();

			Element table = worksheet.getChild("Table", namespace);
			List<Element> rows = table.getChildren("Row", namespace);
			for (Element rowElement : rows) {

				List<Element> cells = rowElement.getChildren("Cell", namespace); 

				int column = 0;
				Row row = new Row();
				for (Element cellElement : cells) {

					column++;
					StringBuilder value = new StringBuilder("");
					String index = cellElement.getAttributeValue("Index", namespace);

					if (index != null) column = new Integer(index).intValue();

					Element data = cellElement.getChild("Data", namespace);

					if (data != null) {
						List<Object> dataContent = data.getContent();
						for (Object dataValue : dataContent) {
							if (dataValue instanceof Text) {
								value.append(((Text) dataValue).getText()); 
							} else if (dataValue instanceof Element) {
								List<Element> dataElements = ((Element) dataValue).getChildren();
								for (Element dataElement : dataElements) {
									value.append(dataElement.getText());
								}
							}
						}					
					}


					switch (column) {
					case 1:
						row.setLocation(value.toString());
						break;
					case 2:
						row.setItem(value.toString());
						break;
					case 3:
						row.setItemType(value.toString());
						break;
					case 4:
						row.setItemFormat(value.toString());
						break;
					case 5:
						row.setContent(value.toString());
						break;
					case 6:
						row.setOptions(value.toString());
						break;
					case 7:
						row.setAction(value.toString());
						break;
					default:
						break;
					}
				}				
				rowsRaw.add(row);
				//				System.out.println(row.toString());
			}
			worksheetRaw.setRows(rowsRaw);
			dataRaw.add(worksheetRaw);
		}

		return dataRaw;
	}
}

